﻿using System;
using System.Linq;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Configuration;

namespace Complete.Data.Test
{
	[TestClass]
	public class UnitTest1
	{
		[AssemblyInitialize]
		public static void InitializeTestAssembly(TestContext context)
		{
			AppDomain.CurrentDomain.SetData("DataDirectory", 
				System.IO.Path.GetFullPath(
					System.IO.Path.Combine(
						System.IO.Directory.GetCurrentDirectory(), 
						"..\\..\\App_Data")));
		}

		SqlConnection conn;

		[TestInitialize]
		public void Setup()
		{
			conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindDB"].ConnectionString);
		}

		[TestMethod]
		public void TestMethod1()
		{
			var context = new NorthwindDataContext(conn);
			var city = "London";
			var c = context.Customers
				.Where(x => x.City == city)
				.Select(x => new { Name = x.ContactName, Phone = x.Phone, Location = new { City = x.City, Country = x.Country } })
				.ToList();
		}

		[TestMethod]
		public void TestMethod2()
		{
			var context = new NorthwindDataContext(conn);
			var c = context.Customers.ToList();
		}

		[TestMethod]
		public void TestMethod3()
		{
			var context = new NorthwindDataContext(conn);
			var c = context.Orders.ToList();
		}

		[TestMethod]
		public void TestMethod4()
		{
			var context = new NorthwindDataContext(conn);
			var city = "London";
			var c = context.Customers
				.Select(x => new { Name = x.ContactName, Phone = x.Phone, Location = new { City = x.City, Country = x.Country } })
				.Where(x => x.Location.City == city)
				.ToList();
		}

		[TestMethod]
		public void TestMethod5()
		{
			var context = new NorthwindDataContext(conn);
			var query =
				from c in context.Customers
				where c.Id == "ALFKI"
				join o in context.Orders on c.Id equals o.CustomerID
				select new { c.ContactName, o.OrderDate };
			query.ToList();
		}

		[TestMethod]
		public void TestMethod6()
		{
			var context = new NorthwindDataContext(conn);
			var query =
				from c in context.Customers
				where c.Id == "ALFKI"
				from o in context.Orders.Where(o => o.CustomerID == c.Id).DefaultIfEmpty()
				select new { c.ContactName, o.OrderDate };
			query.ToList();
		}

		[TestMethod]
		public void TestMethod7()
		{
			var context = new NorthwindDataContext(conn);
			var query =
				from c in context.Customers
				where c.Id == "ALFKI"
				from o in context.Orders.Where(o => o.CustomerID == c.Id)
				select new { c.ContactName, o.OrderDate };
			query.ToList();
		}

		[TestMethod]
		public void TestMethod8()
		{
			var context = new NorthwindDataContext(conn);
			var query =
				from c in context.Customers
				where c.Id == "ALFKI"
				from o in c.Orders
				select new { c.ContactName, o.OrderDate };
			query.ToList();
		}

		[TestMethod]
		public void TestMethod9()
		{
			var context = new NorthwindDataContext(conn);
			var query =
				from o in context.Orders
				where o.Customer.Id == "ALFKI"
				select new { o.Customer.ContactName, o.OrderDate };
			query.ToList();
		}

		[TestMethod]
		public void TestMethod10()
		{
			var context = new NorthwindDataContext(conn);
			var query =
				from c in context.Customers
				let x = "Test"
				let y = c.Phone
				where c.Id == "ALFKI"
				select new { x, y, c.PostalCode, c };
			query.ToList();
		}

		[TestMethod]
		public void TestMethod11()
		{
			var context = new NorthwindDataContext(conn);
			var query =
			  from p in context.Products
			  join s in context.Suppliers on p.Supplier.Id equals s.Id
			  select new { p.ProductName, s.ContactName, s.Phone };

			query.ToList();
		}

		[TestMethod]
		public void TestMethod12()
		{
			var context = new NorthwindDataContext(conn);
			var query =
			  from o in context.Orders
			  select new
			  {
				  o.Freight,
				  AwayFromZero = Math.Round(o.Freight, 1, MidpointRounding.AwayFromZero),
				  ToEven = Math.Round(o.Freight, 1, MidpointRounding.ToEven)
			  };

			query.ToList();
		}

		[TestMethod]
		public void TestMethod13()
		{
			var context = new NorthwindDataContext(conn);
			var query = context.Orders.Take(3);

			var list = query.ToList();
			Assert.IsTrue(list.Count <= 3);
		}

		[TestMethod]
		public void TestMethod14()
		{
			var context = new NorthwindDataContext(conn);
			var query = (
			  from o in context.Orders
			  from d in o.OrderDetails.Take(o.Id)
			  select new
			  {
				  order = o,
				  details = d,
			  }).Take(5);

			var list = query.ToList();
			Assert.IsTrue(list.Count <= 5);
		}

		[TestMethod]
		public void TestMethod15()
		{
			var context = new NorthwindDataContext(conn);
			var query =
				from c in context.Customers.OrderBy(c => c.ContactName)
				from o in context.Orders.OrderBy(o => o.OrderDate)
				where c.Id == o.CustomerID
				select new { c.ContactName, o.OrderDate };

			var list = query.ToList();
			var resorted = list.OrderBy(x => x.ContactName).ThenBy(x => x.OrderDate).ToList();
			Assert.IsTrue(list.SequenceEqual(resorted));
		}

		[TestMethod]
		public void TestMethod17()
		{
			var context = new NorthwindDataContext(conn);
			var query = context
				.Orders
				.OrderBy(o => o.Customer.ContactName)
				.ThenBy(o => o.Employee.LastName)
				.OrderBy(o => o.Id);
			query.ToList();
		}

		[TestMethod]
		public void TestMethod18()
		{
			var context = new NorthwindDataContext(conn);
			var query = context
				.Orders
				.OrderBy(o => o.Customer.ContactName)
				.ThenBy(o => o.Employee.LastName)
				.Select(o => new { o.Id, o.Customer.ContactName, o.Employee.LastName });
			query.ToList();
		}

		[TestMethod]
		public void TestMethod19()
		{
			var context = new NorthwindDataContext(conn);
			var query = context.Orders
			  .OrderByDescending(o => o.Freight * o.Id)
			  .Select(o => new { o.Id, o.CustomerID })
			  .Select(o => new { id = o.Id, custid = o.CustomerID });
			query.ToList();
		}

		[TestMethod]
		public void TestMethod20()
		{
			var context = new NorthwindDataContext(conn);
			var query = context.Orders
			  .OrderBy(o => new { o.Customer.ContactName, x = new { o.Customer, o } })
			  .Select(o => new { id = o.Id, custid = o.CustomerID });
			query.ToList();
		}

		[TestMethod]
		public void TestMethod21()
		{
			var context = new NorthwindDataContext(conn);
			var query = context.Orders
			  .Select(o => o.Id);
			query.ToList();
		}

		[TestMethod]
		public void TestMethod22()
		{
			var context = new NorthwindDataContext(conn);
			var query = context.Orders
			  .Select(o => o.Customer);
			query.ToList();
		}

	}
}
